package developmentTeamScheduling;

import java.sql.*;
import java.util.Scanner;

public class Utils2 {
    /*
     * 工具中的构造方法一般都是私有的。
     * 因为工具类中的方法都是静态的，不需要new对象，直接用类名调用
     * */
    private Utils2() {
    }

    /*
     * 注册驱动只需要注册一次就可以了，不能调用一次方法注册一次
     * 所以用静态代码块，静态代码块在类加载时执行，并且只执行一次
     * */
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    /*
     * 获取数据库连接对象
     * @return连接对象
     * @throws SQLException
     * */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC", "root", "123456");
    }

    /*
     * 关闭资源
     * @param conn连接对象
     * @param ps 数据库操作对象
     * @param rs 结果集
     * */
    public static void close(Connection conn, Statement ps, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /*
     * 初始化用户界面
     * */
    public static void userUI() {
        System.out.println("----------------------------------------------------------------------------");
        System.out.println("******************  欢迎使用团队调度系统，请认真阅读以下使用说明  *****************");
        System.out.println("****************************  功能编号对应的功能：  ****************************" + "\n" +
                "1.表示查看团队所有成员信息。" + "\n" +
                "2.表示添加员工信息。" + "\n" +
                "3.表示删除员工信息。" + "\n" +
                "4.表示修改员工信息。" + "\n" +
                "5.表示查找员工信息。 " + "\n" +
                "6.表示查看公司所有员工信息。 " + "\n" +
                "7.表示查看功能编号。 " + "\n" +
                "0.表示退出系统" + "\n" +
                "功能完善中······ ");
        System.out.println("-----------------------------------------------");
        System.out.println("以下表格是本公司所有员工信息，请执行相关操作。");
        System.out.println("-----------------------------------------------");
        selectAll();
        System.out.println("-----------------------------------------------");
    }

    /*
     * 获取用户输入的编号
     * */
    public static int getNum() {
        Scanner s = new Scanner(System.in);
        System.out.println("**********  请您输入功能编号：***********");
        try {
            int num = Integer.parseInt(s.next());
            return num;
        } catch (NumberFormatException e) {
            return 999;
        }

    }

    /*
     * 判断用户输入功能号
     * */
    public static void judgeNum() {
        while (true) {
            int num = getNum();
            switch (num) {
                case 1:
                    selectTeamUser();
                    break;
                case 2:
                    judgeIncrease();
                    break;
                case 3:
                    decrease();
                    break;
                case 4:
                    modify();
                    break;
                case 5:
                    select();
                    break;
                case 6:
                    selectAll();
                    break;
                case 7:
                    selectFunctionNumber();
                    break;
                case 0:
                    System.out.println("谢谢使用，再见！");
                    return;
                case 999:
                    System.out.println("功能编号输入有误，请检查是否为数字！");
                    break;
                default:
                    System.out.println("本系统暂无此项功能，期待后续增加。");
                    judgeNum();
            }
        }
    }


    /*
     * 功能号1：查询所有团队成员信息
     * */
    public static void selectTeamUser() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
//          获取连接
            conn = Utils2.getConnection();
//            获取数据库操作对象
            stmt = conn.createStatement();
//            执行sql语句
            String sql = "select * from teamuser";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String ID = rs.getString("ID");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String salary = rs.getString("salary");
                String position = rs.getString("position");
                String bonus = rs.getString("bonus");
                String stock = rs.getString("stock");
                System.out.println(ID + "  " + name + "  " + age + "  " + salary + "  " + position + "  " + bonus + "  " + stock);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Utils2.close(conn, stmt, rs);
        }
    }


    /*
     * 功能号2：判断之后添加团队成员
     * */
    public static void judgeIncrease() {
        System.out.println("请输入需要添加团队成员的职位名称和ID号，以回车隔开。（如架构师，程序员，设计师）");
        Scanner s1 = new Scanner(System.in);
        String position = s1.next();
        Scanner s2 = new Scanner(System.in);
        String id = s2.next();
        if (position.equals("架构师")) {
            if (selectPsitionCount(position) < 1) {
                increase(judgeAll(id, position));
            } else {
                System.out.println("架构师数量已达上限！");
            }
        } else if (position.equals("程序员")) {
            if (selectPsitionCount(position) < 3) {
                increase(judgeAll(id, position));
            } else {
                System.out.println("程序员数量已达上限！");
            }
        } else if (position.equals("设计师")) {
            if (selectPsitionCount(position) < 2) {
                increase(judgeAll(id, position));
            } else {
                System.out.println("设计师数量已达上线！");
            }
        } else {
            System.out.println("该成员不是开发成员，无法添加！");
        }

    }

    /*
     * 功能号3：删除团队成员信息
     * */
    public static void decrease() {
        Connection conn = null;
        PreparedStatement ps = null;
        System.out.println("请输入需要删除成员的ID号或姓名：");
        Scanner sc = new Scanner(System.in);
        String deletTeamName = sc.next();
        try {
            conn = Utils2.getConnection();
            System.out.println("是否确定删除" + deletTeamName + "成员？(y/n)");
            Scanner sc2 = new Scanner(System.in);
            String judgeDelete = sc2.next();
            if (judgeDelete.equals("y")) {
                String sql = "delete from teamuser where ID = ? or name = ?";
                ps = conn.prepareStatement(sql);
                ps.setString(1, deletTeamName);
                ps.setString(2, deletTeamName);
                int count = ps.executeUpdate();
                if (count == 1) {
                    System.out.println("删除成功！");
                } else {
                    System.out.println("删除失败，团队中没有该成员！");
                }
            } else {
                System.out.println("取消删除成功！");
            }

        } catch (SQLException e) {
//            e.printStackTrace();
            System.out.println("删除失败，请检查输入数据的有效性后再次删除！");
            decrease();
        } finally {
            Utils2.close(conn, ps, null);
        }
    }


    /*
     * 功能号4：修改团队成员信息
     * */
    public static void modify() {
        Connection conn = null;
        PreparedStatement ps = null;
        System.out.println("请输入需要修改团队成员的ID号：");
        Scanner sc = new Scanner(System.in);
        String modifyID = sc.nextLine();

        String[] strings = judgeAll(modifyID, "no");
        if (strings[0].equals("无")) {
            System.out.println("修改失败!公司没有该员工信息，请确认数据后重新修改！");
            return;
        }
        System.out.println("请输入需要修改的字段和内容，以”，“号间隔。");
        String mofifyContents = sc.nextLine();
        String[] si = mofifyContents.split("，");
        try {
            String columName = si[0];
            String content = si[1];

//        判断用户输入字段
            if (columName.equals("姓名")) {
                columName = "name";
            } else if (columName.equals("年龄")) {
                columName = "age";
            } else if (columName.equals("工资")) {
                columName = "salary";
            } else if (columName.equals("职位")) {
                columName = "position";
            } else if (columName.equals("奖金")) {
                columName = "bonus";
            } else if (columName.equals("股票")) {
                columName = "stock";
            } else {
                System.out.println("输入字段有误，请重新输入！");
            }
            try {
                conn = Utils2.getConnection();
                String sql = "update teamuser set " + columName + " = ?  where ID = ? ";
                ps = conn.prepareStatement(sql);
                ps.setString(1, content);
                ps.setString(2, modifyID);
                int count = ps.executeUpdate();
                if (count == 1) {
                    System.out.println("修改成功！");
                }
            } catch (SQLException e) {
//            e.printStackTrace();
                System.out.println(modifyID + columName + content);
                System.out.println("修改失败，请检查输入数据的有效性后再次修改！");
                modify();
            } finally {
                Utils2.close(conn, ps, null);
            }
        }catch (ArrayIndexOutOfBoundsException e){
            System.out.println("修改失败！请检查输入数据的有效性");
        }

    }

    /*
     * 功能号5：表示查找成员信息
     * */
    public static void select() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入需要查找的成员ID号：");
        String selectID = sc.next();
//        判断是否查询成功
        boolean flag = true;
        try {
            conn = Utils2.getConnection();
            String sql = "select * from teamuser where ID = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, selectID);
            rs = ps.executeQuery();
            while (rs.next()) {
                String ID = rs.getString("ID");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String salary = rs.getString("salary");
                String position = rs.getString("position");
                String bonus = rs.getString("bonus");
                String stock = rs.getString("stock");
                System.out.println(ID + "  " + name + "  " + age + "  " + salary + "  " + position + "  " + bonus + "  " + stock);
                flag = false;
            }
            if (flag) {
                System.out.println("没有查到该员工信息！请检查ID号是否正确");
            }
        } catch (SQLException e) {
//            e.printStackTrace();
            System.out.println("没有查到该员工信息！请检查ID号是否正确");
            select();
        } finally {
            Utils2.close(conn, ps, rs);
        }
    }

    /*
     * 6.判断某职位在该项目中的数量---------
     * */
    public static int selectPsitionCount(String ptn) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
//        架构师、设计师、程序员数量（1，2，3）
        int architectCount = 0;
        int designerCount = 0;
        int programmerCount = 0;
        int count = 0;
        try {
//          获取连接
            conn = Utils2.getConnection();
//            获取数据库操作对象
            stmt = conn.createStatement();
//            执行sql语句
            String sql = "select * from teamuser";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String position = rs.getString("position");
//                System.out.println(position);
                if (position.equals(ptn)) {
                    architectCount += 1;
                    count = architectCount;
                } else if (position.equals(ptn)) {
                    designerCount += 1;
                    count = designerCount;
                } else if (position.equals(ptn)) {
                    programmerCount += 1;
                    count = programmerCount;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Utils2.close(conn, stmt, rs);
        }
        return count;
    }

    /*
     * 7.添加团队成员------
     * */
    public static void increase(String arr2[]) {
//        System.out.println("数组长度：" + arr2.length);
        if (arr2[0].equals("休假")) {
            System.out.println("添加失败!该员工正在休假！请重新添加");
            return;
        }
        if (arr2[0].equals("无")) {
            System.out.println("添加失败!公司没有该员工信息，请确认数据后重新添加！");
            return;
        }
//        判断用户输入职位和员工实际职位是否相对
        if (!(arr2[4].equals(arr2[7]))) {
            System.out.println("输入职位和" + arr2[0] + "号员工职位不符，请检查后重新添加");
            return;
        }
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = Utils2.getConnection();
            String sql = "insert into teamuser(ID, name, age, salary, position, bonus, stock) values (?, ?, ?, ?, ?, ?, ?)";
            ps = conn.prepareStatement(sql);
//            将数据插入数据库
            ps.setString(1, arr2[0]);
            ps.setString(2, arr2[1]);
            ps.setString(3, arr2[2]);
            ps.setString(4, arr2[3]);
            ps.setString(5, arr2[4]);
            ps.setString(6, arr2[5]);
            ps.setString(7, arr2[6]);
            int count = ps.executeUpdate();
            if (count == 1) {
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
//            e.printStackTrace();
            System.out.println("已经存在该员工信息，请重新插入！");
            return;
        } finally {
            Utils2.close(conn, ps, null);
        }


    }

    /*
     * 通过员工ID号判断公司员工中是否存在需要添加的团队员工
     *
     * */
    public static String[] judgeAll(String id, String p) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String[] arr;
//        记录数据库记录条数
        int flag = 0;
        int flag2 = 1;
        try {
//          获取连接
            conn = Utils2.getConnection();
//            获取数据库操作对象
            stmt = conn.createStatement();
//            执行sql语句
            String sql = "select * from companyuser";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String ID = rs.getString("ID");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String salary = rs.getString("salary");
                String position = rs.getString("position");
                String bonus = rs.getString("bonus");
                String stock = rs.getString("stock");
//                String equipments = rs.getString("equipments");
                String statue = rs.getString("statue");
//                通过ID号判断公司是否存在该员工，如果存在则继续判断该员工是否在休假
                if (ID.equals(id) && statue.equals("工作")) {
                    arr = new String[]{ID, name, age, salary, position, bonus, stock, p};
                    return arr;
                } else if (ID.equals(id) && statue.equals("休假")) {
                    return new String[]{"休假"};
                }

            }

        } catch (SQLException e) {
//            e.printStackTrace();
            System.out.println("请确认数据后重新添加！");
        } finally {
            Utils2.close(conn, stmt, rs);
        }

        return new String[]{"无"};
    }

    /*
     * 查询公司所有员工信息
     * */
    public static void selectAll() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
//          获取连接
            conn = Utils2.getConnection();
//            获取数据库操作对象
            stmt = conn.createStatement();
//            执行sql语句
            String sql = "select * from companyuser";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String ID = rs.getString("ID");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String salary = rs.getString("salary");
                String position = rs.getString("position");
                String bonus = rs.getString("bonus");
                String stock = rs.getString("stock");
                String equipments = rs.getString("equipments");
                String statue = rs.getString("statue");
                System.out.println(ID + "  " + name + "  " + age + "  " + salary + "  " + position + "  " + bonus + "  " + stock + "  " + equipments + " " + statue);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Utils2.close(conn, stmt, rs);
        }
    }

    /*
     * 查看功能编号
     * */
    public static void selectFunctionNumber() {
        System.out.println("1.表示查看团队所有成员信息。" + "\n" +
                "2.表示添加员工信息。" + "\n" +
                "3.表示删除员工信息。" + "\n" +
                "4.表示修改员工信息。" + "\n" +
                "5.表示查找员工信息。 " + "\n" +
                "6.表示查看公司所有员工信息。 " + "\n" +
                "7.表示查看功能编号。 " + "\n" +
                "0.表示退出系统" + "\n" +
                "功能完善中······ ");
    }


}
